<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Car_model extends CI_Model 
{ 
    function __construct()
    {
        // Call the Model constructor
        parent::__construct();
    }
	
    public function query_get_car()
    {
        return 
            "SELECT 
                car.id as id,
                car.price as car_price,
                car.quality as quality,
                -- car.image,
                car.description,
                car.number_votes,
                car.total_points,
                car.dec_avg,
                car.whole_avg, 
                car.door_count,
                car.fuel,                
                -- car.vehicle_type,
                drivetrain.drivetrain,
                transmission.transmission,
                car.post_by,
                car.dealer_id,
                car.status,
                car.expiration_date,
                make.name as car_make,
                model.name as car_model,    
                car.year as car_serie,
                type.name as car_type,
                ex_color.color as external_color,
                in_color.color as internal_color,
                dealer.name as dealer_name,
                dealer.company_phone_1,
                dealer.email,
                dealer.company,
                location.province,
                location.district,
                category.category_name as car_category,
                image.image,
                dealer.id as dealer_id
            FROM    
                psacar_car as car
                left join (select car_id, image from psacar_car_image where id in (SELECT min(id) as id FROM `psacar_car_image` group by car_id)) as image on car.id = image.car_id
                -- left join psacar_series as series on car.series_id = series.id 
                left join psacar_model as model on car.model_id = model.id 
                left join psacar_make as make on model.make_id = make.id 
                left join psacar_car_type as type on car.type_id = type.id
                left join psacar_member as dealer on car.dealer_id = dealer.id
                left join psacar_location as location on dealer.location_id = location.id
                left join psacar_color as ex_color on car.external_color_id = ex_color.id
                left join psacar_color as in_color on car.internal_color_id = in_color.id                
                left join psacar_category as category on car.category_id = category.category_id 
                left join psacar_transmission as transmission on car.transmission = transmission.id 
                left join psacar_drivetrain as drivetrain on car.drive_train = drivetrain.id ";
    }
    
    public function get_car_by_id($car_id)
    {
        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.status = 1
                AND car.id = " . $car_id;

        $result = $this->db->query($query_string);
        return  $result;
    }
    
    public function get_car_by_category($car_category)
    {
        $extra = "";
	$operator = " AND ";
        $this->load->model('Track_model');

	if(strlen($car_category) > 0)
        {
            $extra =  $this->db->escape($car_category);	
            $this->Track_model->track_activity("Category", $car_category);
        }
        
        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.status = 1
                AND category.category_name = " . $extra;
                
        $result = $this->db->query($query_string);
        return  $result;
    }
    
    public function get_car_by_make($car_make)
    {
        $extra = "";
		$operator = " AND ";
        $this->load->model('Track_model');
        
        if(strlen($car_make) > 0) {
            $extra =  $this->db->escape($car_make);	
            $this->Track_model->track_activity("Make", $car_make);
        }
        
        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.status = 1
                AND make.name = " . $extra;
                
        $result = $this->db->query($query_string);
        return  $result;
    }
    
    public function get_car_by_member_name($dealer_name)
    {
        $extra = "";
	$operator = " AND ";
	if(strlen($dealer_name) > 0)
            $extra =  $this->db->escape($dealer_name);	
        
        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.status = 1
                AND dealer.id = " . $extra;

        $result = $this->db->query($query_string);
        return  $result;
    }
    
    public function get_approved_car_by_member_id($member_id)
    {        
        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.status = 1
                AND car.expiration_date >= curdate()
                AND dealer.id = " . $member_id;
        
        $result = $this->db->query($query_string);
        return  $result;
    }    
    
    public function get_pending_car_by_member_id($member_id)
    {        
        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.status = 0
                AND dealer.id = " . $member_id;
        
        $result = $this->db->query($query_string);
        return  $result;
    }    

    public function get_expired_car_by_member_id($member_id)
    {        
        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.status = 1
                AND car.expiration_date < curdate()
                AND dealer.id = " . $member_id;
        
        $result = $this->db->query($query_string);
        return  $result;
    }    

    public function get_car_list(
        $make = '', 
        $model = '', 
        $max_price = 0,
        $dealer = '',
        $year = '',
        $location = '',
        $search_type = '%',
        $rent_type = '',
        $duration = '',
        $duration_type = '',
        $driver = '',
        $reference_number = '')
    {
        $extra = " ";
        $operator = " AND ";
        
        $this->load->model('Track_model');        
        if(strlen($make) > 0) {
            $extra .= $operator . "make.id = " . $this->db->escape($make);
            
            $this->db->limit(1);
            $query = $this->db->get_where("psacar_make", array("id"=> $make));
            $make = $query->row();
            $this->Track_model->track_activity("Make", $make->name);
        }
        
        if(strlen($model) > 0) {
            $extra .= $operator . "model.id = " . $this->db->escape($model);
            
            $this->db->limit(1);
            $query = $this->db->get_where("psacar_model", array("id"=> $model));
            $model = $query->row();                        
            $this->Track_model->track_activity("Model", $model->name);
        }
        
        if($max_price > 0) {
            $extra .= $operator . "car.price <= " . $this->db->escape($max_price);            
            $this->Track_model->track_activity("Price", $max_price);
        }

        if(strlen($dealer) > 0) {
            $extra .= $operator . "dealer.company = " . $this->db->escape($dealer);            
            $this->Track_model->track_activity("Dealer", $dealer);
        }

        if(strlen($year) > 0) {
            $extra .= $operator . "car.year = " . $this->db->escape($year);            
            $this->Track_model->track_activity("Year", $year);
        }

        if(strlen($location) > 0) {
            $extra .= $operator . "location.province = " . $this->db->escape($location);            
            $this->Track_model->track_activity("Location", $location);
        }

        if(strlen($rent_type) > 0) {
            $extra .= $operator . "car.rent_type = " . $this->db->escape($rent_type);            
            $this->Track_model->track_activity("Rent type", $rent_type);
        }
        
        if(strlen($duration) > 0) {
            $extra .= $operator . "car.duration = " . $this->db->escape($duration);            
            $this->Track_model->track_activity("Mileage", $duration);
        }

        if(strlen($duration_type) > 0) {
            $extra .= $operator . "car.duration_type = " . $this->db->escape($duration_type);            
            $this->Track_model->track_activity("Rent duration", $duration_type);
        }

        if(strlen($driver) > 0) {
            $extra .= $operator . "car.is_driver = " . $this->db->escape($driver);            
            $this->Track_model->track_activity("Driver included", $driver);
        }
        
        if(strlen($reference_number) > 0) {
            $extra .= $operator . "car.code = " . $this->db->escape($reference_number);            
        }

        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.expiration_date >= curdate()
                AND car.status = 1
                AND type.code LIKE '$search_type'" . $extra . " " .
            "ORDER BY car.total_points DESC ";// . 
            //"LIMIT " . ($offset * $limit) . ", " . $limit;
        //echo $query_string;
        $result = $this->db->query($query_string);
        return  $result;
    }	

    public function get_car_list_advance(
        $make, 
        $model, 
        $maxprice = 0,
        $location = '',
        $dealer = '',
        $from_price = 0,
        $to_price = 0,
        $from_mileage = 0,
        $to_mileage = 0,
        $from_year = 0,
        $to_year = 0,
        $external_color = 0,
        $drive_train = 0,
        $transmission = 0,
        $fuel = '',
        $search_type = '%')
    {
        $extra = " ";
        $operator = " AND ";    
        $this->load->model('Track_model');
        if(strlen($make) > 0)
        {
            $extra .= $operator . "make.id = " . $this->db->escape($make);	
            $this->Track_model->track_activity("Make", $make);
        }
        
        if(strlen($model) > 0)
        {
            $extra .= $operator . "model.id = " . $this->db->escape($model);
            $this->Track_model->track_activity("Model", $model);
        }
        
        if($maxprice > 0)
        {
            $extra .= $operator . "car.price <= " . $this->db->escape($maxprice);
            $this->Track_model->track_activity("Price", $maxprice);
        }

        if(strlen($location) > 0)
        {
            $extra .= $operator . "location.province = " . $this->db->escape($location);
            $this->Track_model->track_activity("Location", $location);
        }
       
        if(strlen($dealer) > 0)
        {
            $extra .= $operator . "dealer.name = " . $this->db->escape($dealer);        
            $this->Track_model->track_activity("Dealer", $dealer);
        }

        if(($from_price > 0) && ($to_price > 0))
        {
            $extra .= $operator . "car.price BETWEEN " . $this->db->escape($from_price) . " AND " . $this->db->escape($to_price);
            $this->Track_model->track_activity("Price", $from_price . ' - ' . $to_price);
        }

        if(($from_mileage > 0) && ($to_mileage > 0))
        {
            $extra .= $operator . "car.quality BETWEEN " . $this->db->escape($from_mileage) . " AND " . $this->db->escape($to_mileage);
            $this->Track_model->track_activity("Mileage", $from_mileage . ' - ' . $to_mileage);
        }

        if(($from_year > 0) && ($to_year > 0))
        {
            $extra .= $operator . "car.year BETWEEN " . $this->db->escape($from_year) . " AND " . $this->db->escape($to_year);
            $this->Track_model->track_activity("Year", $from_year . ' - ' . $to_year);
        }

        if(strlen($external_color) > 0)
        {
            $extra .= $operator . "ex_color.color = " . $this->db->escape($external_color);
            $this->Track_model->track_activity("External color", $external_color);
        }

        if(strlen($drive_train) > 0)
        {
            $extra .= $operator . "drivetrain.drivetrain = " . $this->db->escape($drive_train);
            $this->Track_model->track_activity("Drive train", $drive_train);
        }

        if(strlen($transmission) > 0)
        {
            $extra .= $operator . "transmission.transmission = " . $this->db->escape($transmission);
            $this->Track_model->track_activity("Transmission", $transmission);
        }

        $extra_filter = '';
        if(is_array($fuel)){
            foreach ($this->input->post('fuel') as $a_fuel)
            {
                if(strlen($extra_filter) != 0)
                    $extra_filter .= " OR ";
                
                $extra_filter .= "car.fuel LIKE '%$a_fuel%'";
                
                $this->Track_model->track_activity("Fuel", $a_fuel);
            }
        }
        
        if(strlen($extra_filter) != 0)
            $extra .= $operator . "(" . $extra_filter . ")";
        
        if($search_type != '%')
            $this->Track_model->track_activity("Car type", $search_type);
        
        $query_string = 
            $this->query_get_car() . 
            "WHERE
                car.status = 1
                AND type.name like '$search_type'" . $extra;
        //echo $query_string;
        $result = $this->db->query($query_string);
        return  $result;
    }    
	
    public function car_image($car_id = 0) {
            $car_images = $this->db->get_where('psacar_car_image', array('car_id' => $car_id));
            if ($car_images->num_rows == 0) {
                    echo '<img width="112px" height="84px" src="' . base_url() . 'system/public/assets/cars/no_image.png" />';
            }		
            else {							
                    $first_image = true;
                    foreach($car_images->result() as $image) {
                            if($first_image) {
                                    echo '<a class="gallery" href="' . base_url() . 'system/public/assets/cars/' . $image->image . '"><img width="112px" height="84px" src="' . base_url() . 'system/public/assets/cars/' . $image->image . '" /></a>';

                                    $first_image = false;
                            }
                            else 
                                    echo '<a class="gallery" href="' . base_url() . 'system/public/assets/cars/' . $image->image . '"></a>'; 
                    }
            }		
    }
	
    public function car_fuel($fuel_list = '') {
        $car_fuel = '';
        if(strlen($fuel_list) == 0)
            return $car_fuel;
        
        $fuel_list = preg_split('/,/', $fuel_list, -1, PREG_SPLIT_OFFSET_CAPTURE);        
        foreach($fuel_list as $fuel)
        {
            $this->db->limit(1);
            $query = $this->db->get_where("psacar_fuel", array("id" => $fuel[0]));
            if(strlen($car_fuel) != 0)
                    $car_fuel .= ', ';

            $car_fuel .= $query->row()->fuel;
        }

        echo $car_fuel;		
    }
}